BEGIN TRANSACTION
GO
ALTER TABLE dbo.Voucher ADD
	Temp numeric(11, 2) NOT NULL CONSTRAINT DF_Voucher_Temp DEFAULT 0,
	Weight numeric(11, 2) NOT NULL CONSTRAINT DF_Voucher_Weight DEFAULT 0
GO
COMMIT

USE [DSP]
GO
/****** Object:  View [dbo].[VW_Vouchers]    Script Date: 10/08/2019 12:09:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------*/
ALTER VIEW [dbo].[VW_Vouchers]
AS
SELECT     dbo.Voucher.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.Voucher.SessionID, dbo.Voucher.Remarks AS MasterRemarks, 
                      dbo.Voucher.ReadOnly, dbo.VoucherDetails.VoucherDetailID, dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, dbo.SessionInfo.SessionTitle, 
                      dbo.VoucherDetails.Remarks, dbo.VoucherDetails.Amount AS ActualAmount, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, 
                      dbo.VoucherDetails.DrOrCr, dbo.Accounts.ParentAccount, dbo.Voucher.BankAccountID, dbo.Voucher.LoginID, dbo.Employees.EmployeeName, dbo.Voucher.HostName, 
                      dbo.Voucher.EntryDateTime, dbo.VoucherDetails.TestID, Accounts_2.AccountTitle AS BankAccountTitle, dbo.Tests.TestCode, dbo.Tests.TestName, 
                      dbo.Tests.DisplayTitle, dbo.Tests.UnitTitle, dbo.Tests.NormalValue, dbo.Tests.TestRate, dbo.Tests.GroupID, dbo.Groups.GroupName, dbo.Tests.Type, 
                      dbo.Voucher.CustomerID, Accounts_1.AccountTitle AS CustomerName, dbo.Voucher.ShiftID, dbo.Shifts.ShiftTitle, dbo.Voucher.ReportDate, dbo.Voucher.PatientID, 
                      dbo.Voucher.PatientName, dbo.Voucher.Age, dbo.Voucher.Gender, dbo.Voucher.CellNo, dbo.Voucher.NICNo, dbo.Voucher.Address, 
                      dbo.Voucher.Amount AS VoucherAmount, dbo.Voucher.Expense, dbo.Voucher.Discount, dbo.Voucher.TotalAmount, dbo.Voucher.DrPercent, dbo.Voucher.DrAmount, 
                      dbo.Voucher.NetAmount, dbo.Voucher.CashReceived, dbo.Voucher.Balance, dbo.Voucher.ModifyID, Employees_1.EmployeeName AS ModifyName, 
                      dbo.Voucher.ModifyHostName, dbo.Voucher.ModifyDateTime, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, 
                      dbo.vw_Items.ItemNameUrdu, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Rate, dbo.vw_Items.GroupID AS ItemGroupID, 
                      dbo.vw_Items.GroupName AS ItemGroupName, dbo.Voucher.TestNotes, dbo.Voucher.UpdateDetail, dbo.Voucher.StoreID, dbo.VoucherDetails.BatchNo, 
                      dbo.VoucherDetails.Bonus, dbo.VoucherDetails.STaxPercent, dbo.VoucherDetails.STaxAmount, dbo.VoucherDetails.DiscPercent, dbo.VoucherDetails.DiscAmount, 
                      dbo.VoucherDetails.NetAmount AS Expr1, dbo.VoucherDetails.Packs, dbo.VoucherDetails.PSize, dbo.VoucherDetails.PackRate, dbo.VoucherDetails.ExpiryDate, 
                      dbo.VoucherDetails.QtyW, dbo.VoucherDetails.LoseQty, dbo.Voucher.RVNo, dbo.Voucher.SaleTax, dbo.Voucher.InvDiscPercent, dbo.Voucher.InvDiscount, 
                      dbo.Voucher.PatientType, dbo.Voucher.Temp, dbo.Voucher.Weight
FROM         dbo.Accounts RIGHT OUTER JOIN
                      dbo.Tests INNER JOIN
                      dbo.Groups ON dbo.Tests.GroupID = dbo.Groups.GroupID RIGHT OUTER JOIN
                      dbo.vw_Items RIGHT OUTER JOIN
                      dbo.Employees RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_2 RIGHT OUTER JOIN
                      dbo.VoucherDetails RIGHT OUTER JOIN
                      dbo.Voucher ON dbo.VoucherDetails.VoucherID = dbo.Voucher.VoucherID ON Accounts_2.AccountNo = dbo.Voucher.BankAccountID LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 ON dbo.Voucher.ModifyID = Employees_1.EmployeeID LEFT OUTER JOIN
                      dbo.Shifts ON dbo.Voucher.ShiftID = dbo.Shifts.ShiftID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.Voucher.CustomerID = Accounts_1.AccountNo ON dbo.Employees.EmployeeID = dbo.Voucher.LoginID ON 
                      dbo.vw_Items.ItemID = dbo.VoucherDetails.ItemID ON dbo.Tests.TestID = dbo.VoucherDetails.TestID ON 
                      dbo.Accounts.AccountNo = dbo.VoucherDetails.AccountNo LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID

----------------

USE [DSP]
GO
/****** Object:  View [dbo].[vw_TestDetail]    Script Date: 10/08/2019 12:49:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_TestDetail]
AS
SELECT     dbo.Voucher.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, dbo.Voucher.Remarks, 
                      dbo.Voucher.ReadOnly, dbo.Voucher.BankAccountID, dbo.Accounts.AccountTitle, dbo.Voucher.LoginID, dbo.Employees.EmployeeName, dbo.Voucher.HostName, 
                      dbo.Voucher.EntryDateTime, dbo.Voucher.ShiftID, dbo.Shifts.ShiftTitle, dbo.Voucher.ReportDate, dbo.Voucher.PatientID, dbo.Voucher.PatientName, dbo.Voucher.Age, 
                      dbo.Voucher.Gender, dbo.Voucher.CellNo, dbo.Voucher.Address, dbo.Voucher.Amount, dbo.Voucher.Discount, dbo.Voucher.Expense, dbo.Voucher.TotalAmount, 
                      dbo.Voucher.DrPercent, dbo.Voucher.DrAmount, dbo.Voucher.NetAmount, dbo.Voucher.CashReceived, dbo.Voucher.Balance, dbo.Voucher.ModifyID, 
                      dbo.Voucher.ModifyHostName, dbo.Voucher.ModifyDateTime, dbo.VoucherDetailsTest.TestDetailID, dbo.VoucherDetailsTest.GroupID, 
                      CASE WHEN dbo.Tests.Type = 0 THEN dbo.Tests.TestName ELSE Tests_1.TestName END AS GroupName, 
                      CASE WHEN dbo.Tests.Type = 0 THEN Tests.TestRate ELSE Tests_1.TestRate END AS TestRate, dbo.VoucherDetailsTest.TestID, dbo.Tests.TestCode, 
                      dbo.Tests.TestName, dbo.Tests.Type, CASE WHEN dbo.Tests.Type = 0 THEN dbo.Tests.DisplayTitle ELSE Tests_1.DisplayTitle END AS DisplayTitle, 
                      dbo.Tests.UnitTitle, dbo.Tests.NormalValue, dbo.VoucherDetailsTest.PatientValue, dbo.VoucherDetailsTest.DetailRemarks, dbo.Voucher.TestNotes, 
                      dbo.Voucher.NICNo, dbo.Voucher.StoreID, dbo.Voucher.SaleTax, dbo.Voucher.InvDiscPercent, dbo.Voucher.InvDiscount, dbo.Voucher.PatientType, dbo.Voucher.RVNo, 
                      dbo.Voucher.Temp, dbo.Voucher.Weight
FROM         dbo.Tests LEFT OUTER JOIN
                      dbo.Tests AS Tests_1 ON dbo.Tests.GroupID = Tests_1.TestID RIGHT OUTER JOIN
                      dbo.Voucher INNER JOIN
                      dbo.VoucherDetailsTest ON dbo.Voucher.VoucherID = dbo.VoucherDetailsTest.VoucherID ON dbo.Tests.TestID = dbo.VoucherDetailsTest.TestID LEFT OUTER JOIN
                      dbo.Employees ON dbo.Voucher.LoginID = dbo.Employees.EmployeeID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.Voucher.BankAccountID = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID LEFT OUTER JOIN
                      dbo.Shifts ON dbo.Voucher.ShiftID = dbo.Shifts.ShiftID